package cn.hpclub.server.model;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

public class Organization extends Model<Organization>{
    private static final long        serialVersionUID   = -3195327731783237338L;
    private static final Logger      logger             = LoggerFactory.getLogger(Organization.class);
    public static final Organization dao                = new Organization();

    public static String             ID                 = "id";                                                        // 机构主键
    public static String             NUMCODE            = "NumCode";                                                   // 机构编号
    public static String             PARENTID           = "parentid";                                                  // 机构父主键
    public static String             PARENTNAME         = "parentname";                                                // 机构父名
    public static String             DISPLAYNAME        = "DisplayName";                                               // 机构名
    public static String             DISPLAYORDER       = "DisplayOrder";                                              // 机构排序
    public static String             BUSINESS           = "Business";                                                  // 机构业务
    public static String             DESCRIPTION        = "Description";                                               // 机构描述
    public static String             ENABLED            = "enabled";                                                   // 机构状态
    public static String             MODIDATE           = "ModiDate";                                                  // 机构修改时间
    public static String             ORGTYPE            = "orgtype";                                                   // 机构类型
    public static String             ORGSIGN            = "orgSign";                                                   // 机构标识
    public static String             WEBSITEID          = "WebsiteId";                                                 // 机构网点
    public static String             SALESCENTERID      = "SalesCenterId";                                             // 机构渠道中心主键
    public static String             SALESCENTERNAME    = "SalesCenterName";                                           // 机构渠道中心名
    public static String             CLASSNAME          = "class_name";                                                // 机构网点名
    public static String             CLASSSTATUS        = "class_status";                                              // 机构网点状态
    public static String             DELETE             = "is_deleted";                                                // 软删除
    public static String             KEYWORD            = "keyword";                                                   // 关键字
    public static String             PLAT_FORMID        = "PlatformId";                                                //
    public static String             SIM_CARD           = "SIMCardCount";                                              //
    public static String             CREATE_TIME        = "create_time";
    public static String             CITYCODE           = "CityCode";
    public static String             AREACODE           = "AreaCode";                                                  //
    // 查询列表
    static String                    SQL_REFLASH_SELECT = "SELECT gan.*," + "(select " + CLASSNAME + " from "
            + Constant.Table.ORGANIZATION_CLASS + " where " + Constant.Table.ORGANIZATION_CLASS + "." + ID + "=gan."
            + WEBSITEID + ") as " + CLASSNAME + "," + "(select " + DISPLAYNAME + " from " + Constant.Table.ORGANIZATION
            + " as org where org." + ID + "= gan." + PARENTID + ") as " + PARENTNAME;                                  // 查询
    static String                    SQL_REFLASH_FROM   = "FROM " + Constant.Table.ORGANIZATION + " as gan"
            + " where gan." + ID + ">0";
    static String                    SQL_REFLASH_ORDER  = " order by " + MODIDATE + " desc ";

    // 查询机构树
    static String                    SQL_TREE_SELECT    = "select *";
    static String                    SQL_TREE_TOP       = "(select " + ID + " from " + Constant.Table.ORGANIZATION
            + " where " + DISPLAYNAME + "='大掌柜')";
    static String                    SQL_TREE_PARENT    = " from " + Constant.Table.ORGANIZATION + " where " + PARENTID
            + "=";
    static String                    SQL_TREE_FROM      = SQL_TREE_PARENT + SQL_TREE_TOP + " or " + PARENTID
            + "=(select " + ID + SQL_TREE_PARENT + SQL_TREE_TOP + ")";

    // 导入机构

    String                           SQL_SELECT         = "select *";
    String                           SQL_TABLE          = "from " + Constant.Table.ORGANIZATION;

    public Page<Record> list(int number, int size, String numcode, String displayname, String orgtype){
        try{
            String search = "";
            Object[] p;
            search += " and " + DISPLAYNAME + " like ?";
            search += " and " + NUMCODE + " like ?";
            if(!orgtype.equals("-1")){
                search += " and " + ORGTYPE + "=?";
                p = new Object[] { "%" + displayname + "%", "%" + numcode + "%", orgtype };
            } else{
                p = new Object[] { "%" + displayname + "%", "%" + numcode + "%" };
            }
            return Db.paginate(number, size, SQL_REFLASH_SELECT, SQL_REFLASH_FROM + search, p);

        }
        catch(Exception e){
            e.printStackTrace();
            return new Page(new ArrayList<Record>(), 0, 0, 0, 0);
        }
    }

    public Record previewByNumcode(String numcode){
        try{
            return Db.findFirst(SQL_REFLASH_SELECT + " " + SQL_REFLASH_FROM + " and gan." + NUMCODE + "=?",
                                new Object[] { numcode });

        }
        catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Record preview(String id){
        try{
            return Db.findFirst(SQL_REFLASH_SELECT + " " + SQL_REFLASH_FROM + " and gan." + ID + "=?",
                                new Object[] { id });

        }
        catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    @Before({ Tx.class })
    public boolean delete(String ids){
        boolean success = false;
        try{
            Object[] para = new Object[] { "-1" };
            String[] array = new String[0];
            if(ids != null && !ids.isEmpty()){
                para = array = ids.split(",");
            }
            StringBuffer sb = new StringBuffer();
            sb.append("delete from " + Constant.Table.ORGANIZATION + " where id in(");
            if(array.length > 0){
                for(int i = 0; i < array.length; i++){
                    if(i == array.length - 1){
                        sb.append("?");
                    } else{
                        sb.append("?,");
                    }
                }
                sb.append(")");
                success = Db.update(sb.toString(), para) > 0;
            } else{
                success = false;
            }
        }
        catch(Exception e){
            success = false;
        }
        return success;
    }

    @Before({ Tx.class })
    public void update(Map<String, String[]> p){

        Set<String> k = p.keySet();
        Iterator<String> iterator = k.iterator();
        String pattr = null;
        Record organization = new Record();
        while(iterator.hasNext()){
            pattr = iterator.next();

            if(p.get(pattr) != null && p.get(pattr)[0] != null && p.get(pattr)[0].length() >= 1){

                organization.set(pattr, p.get(pattr)[0]);
            }

        }

        Db.update(Constant.Table.ORGANIZATION, ID, organization);
    }

    public List<Record> tree(int id){

        String sql = " where " + ID + "=? or " + PARENTID + " in (SELECT " + ID + " FROM " + Constant.Table.ORGANIZATION
                + " where " + ID + "=?)" + " or " + PARENTID + " in (SELECT " + ID + " FROM "
                + Constant.Table.ORGANIZATION + " where " + PARENTID + " in (SELECT " + ID + " FROM "
                + Constant.Table.ORGANIZATION + " where " + ID + "=?))";

        return Db.find("select * from " + Constant.Table.ORGANIZATION + sql, new Object[] { id, id, id });

    }

    @Before({ Tx.class })
    public String[] load(HashMap<String, JSONObject> map_level1, HashMap<String, JSONObject> map_level2,
            HashMap<String, JSONObject> map_level3, String sql_website){
        System.gc();
        String sql_org_top = "INSERT INTO organization (" + NUMCODE + "," + DISPLAYNAME + "," + DESCRIPTION + ","
                + MODIDATE + "," + PARENTID + "," + ORGSIGN + "," + ORGTYPE
                + ")  value('100000056','大掌柜','导入机构',date_format(now(),'%Y-%m-%d %H:%i:%s'),'0','1','1')";

        String sql_org_level3 = "INSERT INTO organization (NumCode,DisplayName,Description,ModiDate,parentid,WebsiteId,SalesCenterId,SalesCenterName,enabled,orgSign,orgtype,CityCode,AreaCode,create_time)  values";
        String sql_org_duplicate = " ON DUPLICATE KEY UPDATE ModiDate=date_format(now(),'%Y-%m-%d  %H:%i:%s'),Description='导入机构',DisplayName=values(DisplayName),parentid=values(parentid),CityCode=values(CityCode),AreaCode=values(AreaCode)"
                + ",enabled=values(enabled),WebsiteId=values(WebsiteId),SalesCenterId=values(SalesCenterId),SalesCenterName=values(SalesCenterName)";
        String sql_org_parentname = "(select gan.id from organization as gan where gan.DisplayName='";
        String sql_org_parentid = "(select gan.id from organization as gan where gan.NumCode='";
        String sql_org_websiteid = "(select id from organization_class as gan where WebsiteId=";
        String sql_org_time = "date_format(now(),'%Y-%m-%d %H:%i:%s')";
        String sql_org_city_start = "update organization,(select id,parentid from organization where NumCode in ";
        String sql_org_city_middle = ") as temp set ";
        String sql_org_city_end = "=organization.id where organization.id=temp.id";
        String sql_org;

        JSONObject temp = null;
        String key = null;
        String value = "";
        String parent = "";
        String numcode = "";
        int count_class = 0;
        int count_level1 = 0;
        int count_level2 = 0;
        int count_level3 = 0;
        int map_count_level1 = 0;
        int map_count_level2 = 0;
        int map_count_level3 = 0;
        StringBuffer buffer_value = new StringBuffer();
        StringBuffer buffer_parent = new StringBuffer();
        StringBuffer buffer_numcode = new StringBuffer();
        Set<String> set = null;
        Iterator<String> it = null;
        String msg = "导入文件成功！";
        map_count_level1 = map_level1.size();
        map_count_level2 = map_level2.size();
        map_count_level3 = map_level3.size();
        logger.debug("map_level1=" + map_level1.size());
        logger.debug("map_level2=" + map_level2.size());
        logger.debug("map_level3=" + map_level3.size());
        try{
            // 先导网点
            count_class = Db.update(sql_website);
            logger.debug("count_class=" + count_class);

            // 一级机构
            if(map_level1.size() > 0){
                buffer_value = new StringBuffer();
                buffer_numcode = new StringBuffer("(");
                set = map_level1.keySet();
                it = set.iterator();
                while(it.hasNext()){
                    key = it.next();
                    temp = map_level1.get(key);
                    buffer_value.append("('" + key + "','" + temp.get(Organization.DISPLAYNAME) + "','导入机构',"
                            + sql_org_time + ",");
                    buffer_value.append(sql_org_parentname + "大掌柜'),");
                    buffer_value.append(sql_org_websiteid + temp.get(Organization.WEBSITEID) + "),");
                    buffer_value.append(temp.get(Organization.SALESCENTERID) + ",'"
                            + temp.get(Organization.SALESCENTERNAME) + "',");
                    buffer_value.append(temp.get(Organization.ENABLED)
                            + ",'2','2',0,0,date_format(now(),'%Y-%m-%d %H:%i:%s')),");
                    buffer_numcode.append("'" + key + "',");
                }
                if(buffer_value.length() > 0){
                    value = buffer_value.substring(0, buffer_value.length() - 1);
                    numcode = buffer_numcode.substring(0, buffer_numcode.length() - 1) + ")";
                }
                Db.update(sql_org_level3 + value + sql_org_duplicate + "," + ORGSIGN + "=2," + ORGTYPE + "=2");
                count_level1 = Db.update(sql_org_city_start + numcode + sql_org_city_middle + Organization.CITYCODE
                        + sql_org_city_end);

                logger.debug("count_level1=" + count_level1);
            }

            if(map_level2.size() > 0){
                // 2级机构
                buffer_value = new StringBuffer();
                buffer_numcode = new StringBuffer("(");
                set = map_level2.keySet();
                it = set.iterator();

                while(it.hasNext()){

                    key = it.next();
                    temp = map_level2.get(key);
                    buffer_value.append("('" + key + "','" + temp.getString(DISPLAYNAME) + "','导入机构'," + sql_org_time
                            + ",");
                    buffer_value.append(sql_org_parentid + temp.getString(PARENTID) + "'),");
                    buffer_value.append(sql_org_websiteid + temp.get(Organization.WEBSITEID) + "),");
                    buffer_value.append(temp.get(Organization.SALESCENTERID) + ",'"
                            + temp.get(Organization.SALESCENTERNAME) + "',");
                    buffer_value.append(temp.get(Organization.ENABLED)
                            + ",'0','2',parentid,0,date_format(now(),'%Y-%m-%d %H:%i:%s')),");
                    buffer_numcode.append("'" + key + "',");
                }

                if(buffer_value.length() > 0){
                    value = buffer_value.substring(0, buffer_value.length() - 1);
                    numcode = buffer_numcode.substring(0, buffer_numcode.length() - 1) + ")";
                }
                Db.update(sql_org_level3 + value + sql_org_duplicate + "," + ORGSIGN + "=0," + ORGTYPE + "=2");
                count_level2 = Db.update(sql_org_city_start + numcode + sql_org_city_middle + Organization.AREACODE
                        + sql_org_city_end);
                logger.debug("count_level2=" + count_level2);

            }

            if(map_level3.size() > 0){
                // 3级机构
                set = map_level3.keySet();
                it = set.iterator();
                // int lengh = set.size() / 2;
                // int k = 0;
                // for(int i = 0; i <= lengh; i++){
                // k = 0;
                buffer_value = new StringBuffer();
                buffer_parent = new StringBuffer("(");
                buffer_numcode = new StringBuffer("(");
                while(it.hasNext()){
                    key = it.next();
                    temp = map_level3.get(key);

                    buffer_value.append("('" + key + "','" + temp.getString(DISPLAYNAME) + "','导入机构'," + sql_org_time
                            + ",");
                    buffer_value.append(sql_org_parentid + temp.getString(PARENTID) + "')," + sql_org_websiteid);
                    buffer_value.append(temp.getString(WEBSITEID) + ")," + temp.getString(SALESCENTERID) + ",'"
                            + temp.getString(SALESCENTERNAME) + "','" + temp.getString(ENABLED));
                    buffer_value.append("','0','2',0,parentid,date_format(now(),'%Y-%m-%d %H:%i:%s')),");

                    buffer_parent.append("'" + temp.getString(Organization.PARENTID) + "',");

                    buffer_numcode.append("'" + key + "',");
                    // k++;

                }

                if(buffer_value.length() > 0){
                    value = buffer_value.substring(0, buffer_value.length() - 1);
                    parent = buffer_parent.substring(0, buffer_parent.length() - 1) + ")";
                    numcode = buffer_numcode.substring(0, buffer_numcode.length() - 1) + ")";

                }
                Db.update(sql_org_level3 + value + sql_org_duplicate + "," + ORGSIGN + "=0," + ORGTYPE + "=2");
                count_level3 = Db.update(sql_org_city_start + parent + sql_org_city_middle + Organization.CITYCODE
                        + "= temp.parentid  where organization.parentid = temp.id and NumCode in " + numcode);

                logger.debug("count_level3=" + count_level3);

            }

        }
        catch(Exception e){
            e.printStackTrace();
            msg = "导入文件失败！";
            logger.error(" key=" + key);
            logger.error("organization load temp=" + temp);
            logger.error("organization load count_level3=" + count_level3);
        }
        catch(OutOfMemoryError oom){
            logger.error("organization load 内存溢出!!!!!!!!");
            msg = "内存溢出！";
        }
        finally{
            if(buffer_value != null){
                buffer_value.setLength(0);
                buffer_value = null;
            }
            if(buffer_parent != null){
                buffer_parent.setLength(0);
                buffer_parent = null;
            }
            if(buffer_numcode != null){
                buffer_numcode.setLength(0);
                buffer_numcode = null;
            }

            if(map_level1 != null){
                map_level1.clear();
                map_level1 = null;
            }
            if(map_level2 != null){
                map_level2.clear();
                map_level2 = null;
            }
            if(map_level3 != null){
                map_level3.clear();
                map_level3 = null;
            }

            value = null;
            parent = null;
            numcode = null;
            System.gc();
            if(count_level3 != map_count_level3){
                msg = "数据差别" + (count_level3 - map_count_level3);
            }
            return new String[] { msg, String.valueOf(count_class), String.valueOf(count_level1),
                    String.valueOf(count_level2), String.valueOf(count_level3), };
        }

    }

    public Page<Record> reflash(int number, int size, String keyword, String queryType){
        String operate = " LIKE ";
        Object[] args = new Object[] { "%" + keyword + "%" };
        if("exact".equals(queryType)){
            operate = " = ";
            args = new Object[] { keyword };
        }

        return Db.paginate(number, size, SQL_REFLASH_SELECT,
                           SQL_REFLASH_FROM + " and " + DISPLAYNAME + " " + operate
                                   + "? and gan." + ID + " in " + InterfaceUtil
                                           .getChlidOrgIdSql(InterfaceUtil.getOrgId(), Constant.MAX_ORG_LEVEL, true),
                           args);
    }

    public Page<Record> cityReflash(int number, int size, int themesId, String keyword, String queryType){
        String operate = " LIKE ";
        Object[] args = new Object[] { "%" + keyword + "%", themesId };
        if("exact".equals(queryType)){
            operate = " = ";
            args = new Object[] { keyword, themesId };
        }

        return Db.paginate(number, size, "select org.id,org.NumCode,org.DisplayName ",
                           " from cmcc_city city, organization org  where  city.group_id = org.NumCode and city.region_name "
                                   + operate + " ? and org.id in "
                                   + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), 1, true)
                                   + " and org.id not in (select city_org_id from themes_city where themes_id= ? )",
                           args);
    }

    public Page<Record> dialog(int number, int size, String keyword){

        return Db.paginate(number, size, SQL_REFLASH_SELECT,
                           SQL_REFLASH_FROM + " and " + DISPLAYNAME + " like ? and gan." + ID
                                   + " in " + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(),
                                                                             Constant.MAX_ORG_LEVEL, true),
                           new Object[] { "%" + keyword + "%" });
    }

    public Page<Record> cityDialog(int number, int size, int themesId, String keyword){

        return Db.paginate(number, size, "select org.id,org.NumCode,org.DisplayName ",
                           " from cmcc_city city, organization org  where  city.group_id = org.NumCode and city.region_name like ? and org.id in "
                                   + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), 1, true)
                                   + " and org.id not in (select city_org_id from themes_city where themes_id = ?)",
                           new Object[] { "%" + keyword + "%", themesId });
    }

    public List<Organization> getOrganizationList(){
        String sql = "select * from cartitem where member_id = ? order by createDate desc";
        return Organization.dao.find(sql, getStr("id"));
    }

    /**
     * 获取当前机构下地市列表
     * @return
     */
    public List<Organization> getOrganizationCityList(int orgId){
        String sql = "select * from "+Constant.Table.ORGANIZATION+"  where id=CityCode and enabled=1 and is_deleted=0 and " +
                " id IN(select id FROM organization WHERE id=? OR parentid=?)";
        return Organization.dao.find(sql,orgId,orgId);
    }

    public String getOrgNameByOrgId(int orgId){

        String sql = "select DisplayName from organization where id=?";
        Record r = Db.findFirst(sql, orgId);
        return r == null ? "" : r.getStr("DisplayName");
    }

    public List<Record> getOrganizationTreeList(){
        String sql = "select id,DisplayName,parentid from organization where id = (SELECT id FROM `organization` where DisplayName='大掌柜') or parentid=(SELECT id FROM `organization` where DisplayName='大掌柜') or parentid in(SELECT id FROM `organization` where parentid=(SELECT id FROM `organization` where DisplayName='大掌柜')) ORDER BY parentid asc, id ASC";
        return Db.find(sql);
    }

    public boolean checkUnique(int id, String col, String value){
        try{
            List<Record> result = Db.find("select id from " + Constant.Table.ORGANIZATION + " where " + col + "=?",
                                          new Object[] { value });
            if(result.size() <= 0 || result.get(0).getInt("id") == id){

                return true;
            }

            return false;

        }
        catch(Exception e){
            e.printStackTrace();

            return true;
        }
    }

    public String getNumCodeById(int id){
        Organization rec = dao.findById(id);
        String numCode = "";
        if(rec != null){
            numCode = rec.getStr("NumCode");
        }
        return numCode;
    }

    /**
     * @Title: getStoreCount
     * @Description:获取总的店铺数
     * @return Long
     */
    public Long getStoreCount(){
        String sql = "select count(1) as cnt from organization s where s.enabled = '1' and s.is_deleted = 0 "
                + " and s.parentid in (select id from organization o "
                + " where o.parentid  in (select id from organization org where org.parentid = (select id from organization where orgtype = 1)));";

        Record store = Db.findFirst(sql);
        Long cnt = store.get("cnt");

        return cnt;
    }
}
